/*  `shopid` int,
    `userid` int,
    `areaid` int,
    `shopname` string,
    `shoplevel` tinyint,
    `status` tinyint,
    `createtime` string,
    `modifytime` string,
    `start_dt` string,
    `end_dt` string
*/

/* 初始化hql 初始化的日期2020-07-12 */
insert overwrite table dim.dim_trade_shops
select shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime,
    case  when modifytime is not null 
    then substr(modifytime,1,10)
    else substr(createtime,1,10) 
    end as start_dt, 
    '9999-12-31' as end_dt
from ods.ods_trade_shops where dt='2020-07-12';

/*增量更新*/
drop table if exists tmp.tmp_trade_shops;
create table tmp.tmp_trade_shops as
(
    select 
        dim.shopid, dim.userid, dim.areaid, dim.shopname, dim.shoplevel, dim.status, dim.createtime, dim.modifytime,
        dim.start_dt,
        case when ods.shopid is not null and dim.modifytime<=>ods.modifytime then dim.end_dt --当日没有变化的记录
        else '$do_date'                                                                      --删除的、当日变化的记录的旧纪录
        end as end_dt
    from dim.dim_trade_shops dim left join (select * from ods.ods_trade_shops where dt='$do_date') ods
    on dim.shopid=ods.shopid
    union all
    select
        ods.shopid, ods.userid, ods.areaid, ods.shopname, ods.shoplevel, ods.status, ods.createtime, ods.modifytime,
        case  when ods.modifytime is not null 
            then substr(ods.modifytime,1,10)
            else substr(ods.createtime,1,10) 
        end as start_dt,
        '9999-12-31' as end_dt                                                               --新增的、当日变化记录的新记录
    from (select * from ods.ods_trade_shops where dt='$do_date') ods left join (select * from dim.dim_trade_shops where end_dt>='9999-12-31')dim
    on dim.shopid=ods.shopid
    where dim.shopid is null or (not (ods.modifytime <=> dim.modifytime ))
);
insert overwrite table dim.dim_trade_shops select * from tmp.tmp_trade_shops;

/*回滚 */
insert overwrite table dim.dim_trade_shops(
    select 
        shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime,start_dt, end_dt
    from dim.dim_trade_shops where end_dt<'$rollback_date'                      -- end_dt 小于 rollback date 记录的在rollback日期前已经失效，无需修改
    union all
    select shopid, userid, areaid, shopname, shoplevel, status, createtime, modifytime,start_dt, '9999-12-31' as end_dt
    from dim.dim_trade_shops where start_dt<='$rollback_date' and '$rollback_date'<=end_dt;)
-- start_dt <= rollback date <= end_dt 记录在rollback 前生效，回滚后应该时生效的数据
-- rollback_date <= start_dt 记录在rollbalck后生效，回滚不保留